package com.example.rfidserver.utils;


import android.content.Context;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;

import androidx.annotation.Nullable;

import com.example.rfidserver.db.RfidSystemDBContract;
import com.example.rfidserver.home.HomeActivity;
import com.example.rfidserver.pojo.User;

import java.io.File;
import java.io.FileInputStream;
import java.sql.Connection;
import java.sql.DriverManager;

/*怎么根据db这个目录给出的所有信息，
来创建一个DBHelper类
* */
public class DbHelper  extends SQLiteOpenHelper {

    private final static String DB_PATH = "/data/data/com.example.rfidserver/files/a.db";
    private final static int DB_VERSION = 1;
    private final static String TEXT_TYPE = " TEXT";
    private final static String REAL_TYPE = " REAL";
    private final static String INTEGER_TYPE = " INTEGER";
    private final static String FOREIGN_KEY = "FOREIGN KEY (ticket_id) REFERENCES ticket(ticket_id) ON DELETE CASCADE ON UPDATE CASCADE\n" +
            "FOREIGN KEY (consume_Id) REFERENCES consume(consume_Id) ON DELETE CASCADE ON UPDATE CASCADE";
    //database info


    //创建ticket表的sql
    private final static String SQL_TABLE_TICKET_CREATE_STATEMENT =
            "create table " + RfidSystemDBContract.Ticket.TABLE_NAME +
                    " ( " + RfidSystemDBContract.Ticket.COLUMN_NAME_TICKET_ID +
                    INTEGER_TYPE + " PRIMARY KEY AUTOINCREMENT NOT NULL" + "," +
                    RfidSystemDBContract.Ticket.COLUMN_NAME_TICKET_TYPE + TEXT_TYPE + "," +
                    RfidSystemDBContract.Ticket.COLUMN_NAME_TICKET_DESCRIPTION + TEXT_TYPE
            +" );";
    //创建consume表的sql
    private final static String SQL_TABLE_CONSUME_CREATE_STATEMENT =
            "create table " + RfidSystemDBContract.Consume.TABLE_NAME +
                    " ( " + RfidSystemDBContract.Consume.COLUMN_NAME_CONSUME_ID +
                    INTEGER_TYPE + " PRIMARY KEY AUTOINCREMENT NOT NULL" + "," +
                    RfidSystemDBContract.Consume.COLUMN_NAME_CONSUME_ITEM_NAME + TEXT_TYPE + "," +
                    RfidSystemDBContract.Consume.COLUMN_NAME_CONSUME_ITEM_INTEGRAL + REAL_TYPE
                    +" );";
    //创建user表的sql
    private final static String SQL_TABLE_USER_CREATE_STATEMENT =
            "create table " + RfidSystemDBContract.User.TABLE_NAME +
                    " ( " + RfidSystemDBContract.User.COLUMN_NAME_ID +
                    INTEGER_TYPE + " PRIMARY KEY AUTOINCREMENT NOT NULL" + "," +
                    RfidSystemDBContract.User.COLUMN_NAME_R_ID + TEXT_TYPE + "," +
                    RfidSystemDBContract.User.COLUMN_NAME_TICKET_ID + INTEGER_TYPE +"," +
                    RfidSystemDBContract.User.COLUMN_NAME_INTERGRAL_BILL + REAL_TYPE + "," +
                    RfidSystemDBContract.User.COLUMN_NAME_ROLL + INTEGER_TYPE + "," +
                    RfidSystemDBContract.User.COLUMN_NAME_CONSUME_ID + INTEGER_TYPE + "," +
                    FOREIGN_KEY + ");";
    //测试数据
    String sql = "insert into ticket values (1,'LongTing','龙亭公园');";
    String sql2 = "insert into ticket values (2,'TianBoYangFu','天波杨府');";
    String sql4 = "insert into ticket values (3,'ZhongGuoHanYuan','中国翰园');";
    String sql5 = "insert into ticket values (4,'QingMingShangHeYuan','清明上河园');";
    String sql7 = "insert into ticket values (5,'WanSuiShan','万岁山');";
    String sql8 = "insert into consume (consume_Id,consume_Item_name,consume_Item_integral) values (1,'register',80.0);";
    String sql9 = "insert into consume (consume_Id,consume_Item_name,consume_Item_integral) values (2,'Boating',40.0);";
    String sql10 = "insert into consume (consume_Id,consume_Item_name,consume_Item_integral) values (3,'show',40.0);";
    String sql11 = "insert into consume (consume_Id,consume_Item_name,consume_Item_integral) values (4,'sightseeing bus',20.0);";
    String sql12 = "insert into user  (id,r_id,ticket_id,integral_bill,roll,consume_Id) values (111122223333441,'0CEE5F4500F8',3,1000.0,0,2);";
    String sql13 = "insert into user  (id,r_id,ticket_id,integral_bill,roll,consume_Id) values (111122223333442,'0CEE5F4501F9',2,1000.0,0,3);";
    String sql14 = "insert into user  (id,r_id,ticket_id,integral_bill,roll,consume_Id) values (111122223333443,'0CEE5F4502FA',1,1000.0,0,4);";
    String sql15 = "insert into user  (id,r_id,ticket_id,integral_bill,roll,consume_Id) values (111122223333444,'0CEE5F4503FB',5,1000.0,0,4);";
    String sql16 = "insert into user  (id,r_id,integral_bill,roll,consume_Id) values (111122223333445,'0CEE5F4504FC',0,1,1);";
    String sql17 = "insert into user  (id,r_id,integral_bill,roll,consume_Id) values (111122223333446,'0CEE5F4505FD',0,1,1);";
    String sql18 = "insert into user  (id,roll) values (111122223333447,2);";
    String sql19 = "insert into user  (id,r_id,integral_bill,roll,consume_Id) values (111122223333448,'0CEE5F4507FF',0,1,1);";
    String sql20 = "insert into user  (id,r_id,integral_bill,roll,consume_Id) values (111122223333449,'0CEE5F4508F0',0,1,1);";
    String sql21 = "insert into user  (id,r_id,ticket_id,integral_bill,roll,consume_Id) values (111122223333410,'0CEE5F4509F1',2,1000.0,0,3);";








    public DbHelper(@Nullable Context context, @Nullable String name, @Nullable SQLiteDatabase.CursorFactory factory, int version) {
        super(context, DB_PATH, null, DB_VERSION);
    }



    //编写创建数据库的sql
    //2个回调方法

    //如果数据库不存在，则默认会掉这个方法
    @Override
    public void onCreate(SQLiteDatabase db) {

        //创建表
        db.execSQL(SQL_TABLE_TICKET_CREATE_STATEMENT);
        db.execSQL(SQL_TABLE_CONSUME_CREATE_STATEMENT);
        db.execSQL(SQL_TABLE_USER_CREATE_STATEMENT);
        //插入测试数据
        db.execSQL(sql);
        db.execSQL(sql2);
        db.execSQL(sql4);
        db.execSQL(sql5);
        db.execSQL(sql7);
        db.execSQL(sql8);
        db.execSQL(sql9);
        db.execSQL(sql10);
        db.execSQL(sql11);
        db.execSQL(sql12);
        db.execSQL(sql13);
        db.execSQL(sql14);
        db.execSQL(sql15);
        db.execSQL(sql16);
        db.execSQL(sql17);
        db.execSQL(sql18);
        db.execSQL(sql19);
        db.execSQL(sql20);
        db.execSQL(sql21);



    }

    //数据库版本更新默认调用该方法
    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {

    }
}
